const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// 创建数据库连接
const dbPath = path.join(__dirname, 'fushen.db');
const db = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('Error opening database:', err.message);
  } else {
    console.log('Connected to the SQLite database.');
    initializeDatabase();
  }
});

// 初始化数据库表
function initializeDatabase() {
  // 创建用户表
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`, (err) => {
    if (err) {
      console.error('Error creating users table:', err.message);
    } else {
      console.log('Users table created or already exists.');
    }
  });

  // 创建文档表（包含文件夹和文档）
  db.run(`CREATE TABLE IF NOT EXISTS documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    type TEXT DEFAULT 'document' CHECK (type IN ('folder', 'document')),
    parent_id INTEGER DEFAULT NULL,
    owner_id INTEGER,
    sort_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES documents (id) ON DELETE CASCADE,
    FOREIGN KEY (owner_id) REFERENCES users (id)
  )`, (err) => {
    if (err) {
      console.error('Error creating documents table:', err.message);
    } else {
      console.log('Documents table created or already exists.');
      
      // 检查并添加新字段（如果不存在）
      addColumnIfNotExists('documents', 'type', 'TEXT DEFAULT \'document\'');
      addColumnIfNotExists('documents', 'parent_id', 'INTEGER DEFAULT NULL');
      addColumnIfNotExists('documents', 'sort_order', 'INTEGER DEFAULT 0');
    }
  });

  // 创建文档协作表
  db.run(`CREATE TABLE IF NOT EXISTS document_collaborators (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    document_id INTEGER,
    user_id INTEGER,
    role TEXT DEFAULT 'editor',
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents (id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users (id)
  )`, (err) => {
    if (err) {
      console.error('Error creating document_collaborators table:', err.message);
    } else {
      console.log('Document collaborators table created or already exists.');
    }
  });

  // 创建主题(topic)表
  db.run(`CREATE TABLE IF NOT EXISTS topics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    color TEXT DEFAULT '#42b983',
    icon TEXT,
    created_by INTEGER,
    is_default BOOLEAN DEFAULT FALSE,
    sort_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users (id)
  )`, (err) => {
    if (err) {
      console.error('Error creating topics table:', err.message);
    } else {
      console.log('Topics table created or already exists.');
      
      // 创建默认主题
      createDefaultTopics();
    }
  });

  // 为documents表添加topic_id字段
  addColumnIfNotExists('documents', 'topic_id', 'INTEGER DEFAULT NULL REFERENCES topics(id)');
}

// 添加字段的辅助函数
function addColumnIfNotExists(tableName, columnName, columnDef) {
  // 检查列是否存在
  db.get(`PRAGMA table_info(${tableName})`, (err, result) => {
    if (err) {
      console.error('Error checking table info:', err.message);
      return;
    }
    
    // 获取所有列信息
    db.all(`PRAGMA table_info(${tableName})`, (err, columns) => {
      if (err) {
        console.error('Error getting table columns:', err.message);
        return;
      }
      
      const columnExists = columns.some(col => col.name === columnName);
      
      if (!columnExists) {
        db.run(`ALTER TABLE ${tableName} ADD COLUMN ${columnName} ${columnDef}`, (err) => {
          if (err) {
            console.error(`Error adding column ${columnName} to ${tableName}:`, err.message);
          } else {
            console.log(`Column ${columnName} added to ${tableName} table.`);
          }
        });
      }
    });
  });
}

// 创建默认主题(topics)
function createDefaultTopics() {
  const defaultTopics = [
    {
      title: '工作文档',
      description: '工作相关的文档和记录',
      color: '#42b983',
      icon: '💼',
      is_default: 1
    },
    {
      title: '学习笔记',
      description: '学习资料和笔记',
      color: '#2196f3',
      icon: '📚',
      is_default: 0
    },
    {
      title: '项目管理',
      description: '项目管理和进度跟踪',
      color: '#ff9800',
      icon: '📈',
      is_default: 0
    },
    {
      title: '个人日记',
      description: '个人的想法和记录',
      color: '#9c27b0',
      icon: '📝',
      is_default: 0
    },
    {
      title: '技术文档',
      description: '技术相关的文档和教程',
      color: '#607d8b',
      icon: '🛠️',
      is_default: 0
    }
  ];
  
  defaultTopics.forEach(topic => {
    // 检查是否已存在
    db.get('SELECT id FROM topics WHERE title = ?', [topic.title], (err, row) => {
      if (err) {
        console.error('Error checking topic:', err.message);
        return;
      }
      
      if (!row) {
        // 创建主题
        const sql = `INSERT INTO topics (title, description, color, icon, is_default) 
                     VALUES (?, ?, ?, ?, ?)`;
        db.run(sql, [topic.title, topic.description, topic.color, topic.icon, topic.is_default], (err) => {
          if (err) {
            console.error('Error creating default topic:', err.message);
          } else {
            console.log(`Default topic '${topic.title}' created.`);
          }
        });
      }
    });
  });
}

// 用户相关操作
const User = {
  // 创建用户
  create: (username, password, callback) => {
    const sql = 'INSERT INTO users (username, password) VALUES (?, ?)';
    db.run(sql, [username, password], function(err) {
      callback(err, this ? this.lastID : null);
    });
  },

  // 根据用户名查找用户
  findByUsername: (username, callback) => {
    const sql = 'SELECT * FROM users WHERE username = ?';
    db.get(sql, [username], (err, row) => {
      callback(err, row);
    });
  },

  // 根据ID查找用户
  findById: (id, callback) => {
    const sql = 'SELECT * FROM users WHERE id = ?';
    db.get(sql, [id], (err, row) => {
      callback(err, row);
    });
  }
};

// 文档相关操作
const Document = {
  // 创建文档或文件夹
  create: (title, content, type, parentId, ownerId, topicId, callback) => {
    const sql = 'INSERT INTO documents (title, content, type, parent_id, owner_id, topic_id) VALUES (?, ?, ?, ?, ?, ?)';
    db.run(sql, [title, content, type || 'document', parentId, ownerId, topicId], function(err) {
      callback(err, this ? this.lastID : null);
    });
  },

  // 获取用户的所有文档（树形结构）
  findByUserId: (userId, callback) => {
    const sql = 'SELECT * FROM documents WHERE owner_id = ? ORDER BY type DESC, sort_order ASC, created_at ASC';
    db.all(sql, [userId], (err, rows) => {
      if (err) {
        callback(err, null);
        return;
      }
      // 构建树形结构
      const tree = buildTree(rows);
      callback(null, tree);
    });
  },

  // 获取用户在特定主题下的所有文档（树形结构）
  findByUserIdAndTopic: (userId, topicId, callback) => {
    const sql = 'SELECT * FROM documents WHERE owner_id = ? AND topic_id = ? ORDER BY type DESC, sort_order ASC, created_at ASC';
    db.all(sql, [userId, topicId], (err, rows) => {
      if (err) {
        callback(err, null);
        return;
      }
      // 构建树形结构
      const tree = buildTree(rows);
      callback(null, tree);
    });
  },

  // 获取指定父级的子项
  findByParentId: (parentId, userId, callback) => {
    const sql = 'SELECT * FROM documents WHERE parent_id = ? AND owner_id = ? ORDER BY type DESC, sort_order ASC, created_at ASC';
    db.all(sql, [parentId, userId], (err, rows) => {
      callback(err, rows);
    });
  },

  // 根据ID获取文档
  findById: (id, callback) => {
    const sql = 'SELECT * FROM documents WHERE id = ?';
    db.get(sql, [id], (err, row) => {
      callback(err, row);
    });
  },

  // 更新文档
  update: (id, title, content, callback) => {
    const sql = 'UPDATE documents SET title = ?, content = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?';
    db.run(sql, [title, content, id], function(err) {
      callback(err, this ? this.changes : null);
    });
  },

  // 删除文档或文件夹（级联删除）
  delete: (id, callback) => {
    const sql = 'DELETE FROM documents WHERE id = ?';
    db.run(sql, [id], function(err) {
      callback(err, this ? this.changes : null);
    });
  },

  // 移动文档到不同的父级
  move: (id, newParentId, callback) => {
    const sql = 'UPDATE documents SET parent_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?';
    db.run(sql, [newParentId, id], function(err) {
      callback(err, this ? this.changes : null);
    });
  },

  // 构建树形结构
  buildTree: (items) => {
    return buildTree(items);
  }
};

// 构建树形结构的辅助函数
function buildTree(items, parentId = null) {
  const tree = [];
  items.forEach(item => {
    if (item.parent_id === parentId) {
      const children = buildTree(items, item.id);
      if (children.length > 0) {
        item.children = children;
      }
      tree.push(item);
    }
  });
  return tree;
}

// 文档协作相关操作
const DocumentCollaborator = {
  // 添加协作者
  addCollaborator: (documentId, userId, role, callback) => {
    const sql = 'INSERT INTO document_collaborators (document_id, user_id, role) VALUES (?, ?, ?)';
    db.run(sql, [documentId, userId, role], function(err) {
      callback(err, this ? this.lastID : null);
    });
  },

  // 获取文档的所有协作者
  findByDocumentId: (documentId, callback) => {
    const sql = `
      SELECT dc.*, u.username 
      FROM document_collaborators dc 
      JOIN users u ON dc.user_id = u.id 
      WHERE dc.document_id = ?
    `;
    db.all(sql, [documentId], (err, rows) => {
      callback(err, rows);
    });
  }
};

// 主题(Topic)相关操作
const Topic = {
  // 创建主题
  create: (title, description, color, icon, createdBy, callback) => {
    const sql = `INSERT INTO topics (title, description, color, icon, created_by) 
                 VALUES (?, ?, ?, ?, ?)`;
    db.run(sql, [title, description, color, icon, createdBy], function(err) {
      callback(err, this ? this.lastID : null);
    });
  },

  // 获取所有主题
  findAll: (callback) => {
    const sql = 'SELECT * FROM topics ORDER BY is_default DESC, sort_order ASC, created_at ASC';
    db.all(sql, [], (err, rows) => {
      callback(err, rows);
    });
  },

  // 根据ID获取主题
  findById: (id, callback) => {
    const sql = 'SELECT * FROM topics WHERE id = ?';
    db.get(sql, [id], (err, row) => {
      callback(err, row);
    });
  },

  // 获取默认主题
  findDefault: (callback) => {
    const sql = 'SELECT * FROM topics WHERE is_default = 1 LIMIT 1';
    db.get(sql, [], (err, row) => {
      callback(err, row);
    });
  },

  // 更新主题
  update: (id, title, description, color, icon, callback) => {
    const sql = `UPDATE topics SET title = ?, description = ?, color = ?, icon = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`;
    db.run(sql, [title, description, color, icon, id], function(err) {
      callback(err, this ? this.changes : null);
    });
  },

  // 删除主题
  delete: (id, callback) => {
    // 先检查是否有文档使用该主题
    db.get('SELECT COUNT(*) as count FROM documents WHERE topic_id = ?', [id], (err, row) => {
      if (err) {
        callback(err, null);
        return;
      }
      
      if (row.count > 0) {
        callback(new Error('无法删除，该主题下还有文档'), null);
        return;
      }
      
      const sql = 'DELETE FROM topics WHERE id = ? AND is_default = 0'; // 防止删除默认主题
      db.run(sql, [id], function(err) {
        callback(err, this ? this.changes : null);
      });
    });
  },

  // 设置默认主题
  setDefault: (id, callback) => {
    // 先取消所有默认状态
    db.run('UPDATE topics SET is_default = 0', (err) => {
      if (err) {
        callback(err, null);
        return;
      }
      
      // 设置新的默认主题
      db.run('UPDATE topics SET is_default = 1 WHERE id = ?', [id], function(err) {
        callback(err, this ? this.changes : null);
      });
    });
  },

  // 获取主题下的文档数量
  getDocumentCount: (id, callback) => {
    const sql = 'SELECT COUNT(*) as count FROM documents WHERE topic_id = ?';
    db.get(sql, [id], (err, row) => {
      callback(err, row ? row.count : 0);
    });
  }
};

module.exports = {
  db,
  User,
  Document,
  DocumentCollaborator,
  Topic
};